<?php
defined('SYS_DB_PATH') or define('SYS_DB_PATH', './runtime/backup/');
class ManageDbPDO
{
    private $config;
    private $handler;
    private $tables;    //需要备份的表
    private $begin;     //开始时间
    private $error;     //错误信息

    public function __construct($config)
    {
        $this->config = array();
        $this->tables = array();
        $config['type'] = isset($config['type']) ? $config['type'] : 'mysql';
        $config['hostname'] = isset($config['hostname']) ? $config['hostname'] : '127.0.0.1';
        $config['hostport'] = isset($config['hostport']) ? $config['hostport'] : '3306';
        $config['database'] = isset($config['database']) ? $config['database'] : '';
        $config['username'] = isset($config['username']) ? $config['username'] : 'root';
        $config['password'] = isset($config['password']) ? $config['password'] : '123456';
        $config['charset'] = isset($config['charset']) ? $config['charset'] : 'utf8';
        $config['path'] = isset($config['path']) ? $config['path'] : SYS_DB_PATH;
        $config["backname"] = isset($config['backname']) ? $config['backname'] : date("Y-m-d-H_i_s", time()) . ".sql"; // 默认保存文件
        $this->config = $config;
        $this->begin = microtime(true);
        //@header("Content-type:text/html;charset=utf-8");
        $this->connect();
    }

    //首次进行pdo连接
    private function connect()
    {
        try {
            $this->handler = new PDO(
                "{$this->config['type']}:host={$this->config['hostname']};port={$this->config['hostport']};dbname={$this->config['database']};",
                $this->config['username'],
                $this->config['password'],
                array(
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$this->config['charset']};",
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                ));
        } catch (PDOException $e) {
            die("Error!:" . $e->getMessage() . "<br/>");
        }
    }

    /**
     *查询
     * @paramstring $sql
     * @returnmixed
     */
    private function query($sql = '')
    {
        $stmt = $this->handler->query($sql);
        $stmt->setFetchMode(PDO::FETCH_NUM);
        $list = $stmt->fetchAll();
        return $list;
    }

    /**
     *获取全部表
     * @paramstring $dbName
     * @returnarray
     */
    private function get_dbname($dbName = '*')
    {
        $sql = 'SHOW TABLES';
        $list = $this->query($sql);
        $tables = array();
        foreach ($list as $value) {
            $tables[] = $value[0];
        }
        return $tables;
    }

    /**
     *获取表定义语句
     * @paramstring $table
     * @returnmixed
     */
    private function get_dbhead($table = '')
    {
        $sql = "SHOW CREATE TABLE`{$table}`";
        $ddls = $this->query($sql);
        $ddl = isset($ddls[0][1]) ? $ddls[0][1] . ';' : ';';
        return $ddl;
    }

    /**
     *获取表数据
     * @paramstring $table
     * @returnmixed
     */
    private function get_dbdata($table = '')
    {
        $sql = "SHOW COLUMNS FROM`{$table}`";
        $list = $this->query($sql);
        //字段
        $columns = '';
        //需要返回的SQL
        $query = '';
        foreach ($list as $value) {
            $columns .= "`{$value[0]}`,";
        }
        $columns = substr($columns, 0, -1);
        $data = $this->query("SELECT * FROM`{$table}`");
        foreach ($data as $value) {
            $dataSql = '';
            foreach ($value as $v) {
                $dataSql .= "'{$v}',";
            }
            $dataSql = substr($dataSql, 0, -1);
            $query .= "INSERT INTO`{$table}`({$columns}) VALUES ({$dataSql});".PHP_EOL;
        }
        return $query;
    }

    /**
     *写入文件
     * @paramarray $tables
     * @paramarray $ddl
     * @paramarray $data
     */
    private function writeToFile($tables = array(), $ddl = array(), $data = array())
    {
        $str = "-- ================================".PHP_EOL;
        $str .= "-- MySQL Database Backup Tools".PHP_EOL;
        $str .= "-- Server:{$this->config['hostname']}:{$this->config['hostport']}".PHP_EOL;
        $str .= "-- Database:{$this->config['database']}".PHP_EOL;
        $str .= "-- Data:".date('Y-m-d H:i:s').PHP_EOL;
        $str .= "-- SET FOREIGN_KEY_CHECKS=0;".PHP_EOL;
        $str .= "-- ================================".PHP_EOL.PHP_EOL;
        $i = 0;
        foreach ($tables as $table) {
            $str .= PHP_EOL;
            $str .= "-- ----------------------------".PHP_EOL;
            $str .= "-- Table structure for {$table}".PHP_EOL;
            $str .= "-- ----------------------------".PHP_EOL;
            $str .= PHP_EOL;
            $str .= "DROP TABLE IF EXISTS `{$table}`;".PHP_EOL;
            $str .= $ddl[$i].PHP_EOL;
            $str .= PHP_EOL;
            $str .= "-- ----------------------------".PHP_EOL;
            $str .= "-- Records of {$table}".PHP_EOL;
            $str .= "-- ----------------------------".PHP_EOL;
            $str .= PHP_EOL;
            $str .= $data[$i].PHP_EOL;
            $i++;
        }

        if (!file_exists($this->config['path'])) {
            mkdir($this->config['path']);
        }
        return file_put_contents($this->config['path'] . $this->config['backname'], $str) ? true : false;
        //return file_put_contents( $this->config['path']. $this->config['backname'], $str)?'备份成功!花费时间'.round(microtime(true)- $this->begin,2).'ms':'备份失败!';
    }

    /**
     *设置要备份的表
     * @paramarray $tables
     */
    private function setTables($tables = array())
    {
        if (!empty($tables) && is_array($tables)) {
            //备份指定表
            $this->tables = $tables;
        } else {
            //备份全部表
            $this->tables = $this->get_dbname();
        }
    }

    /**
     *备份
     * @paramarray $tables
     * @returnbool
     */
    public function backup($tables = array())
    {
        //存储表定义语句的数组
        $ddl = array();
        //存储数据的数组
        $data = array();
        $this->setTables($tables);
        if (empty($this->tables)) {
            $this->error = '数据库中没有表!';
            $result['code'] = 400;
            $result['msg'] = $this->error;
            return $result;
        }
        foreach ($this->tables as $table) {
            $ddl[] = $this->get_dbhead($table);
            $data[] = $this->get_dbdata($table);
        }
        //开始写入
        $r = $this->writeToFile($this->tables, $ddl, $data);
        if(!$r){
            $result['code'] = 500;
            $result['msg'] = 'fail';
            return $result;
        }
        $result['code'] = 200;
        $result['msg'] = 'success';
        return $result;
    }

    /**
     *错误信息
     * @returnmixed
     */
    public function getError()
    {
        return $this->error;
    }

    /**
     *还原方法
     */
    public function restore($filename = '')
    {
        $path = $this->config['path'] . $filename;
        if (!file_exists($path)) {
            // $this->error('SQL文件不存在!');
            //return false;
            $result['code'] = 400;
            $result['msg'] = 'SQL文件不存在!';
            return $result;
        }
        $sql = $this->parseSQL($path);
        //dump( $sql);die;
        try {
            $this->handler->exec($sql);
            //echo'还原成功!花费时间',round(microtime(true)- $this->begin,2).'ms';
            $result['code'] = 200;
            $result['msg'] = '还原成功';
            return $result;
        } catch (PDOException $e) {
            // $this->error= $e->getMessage();
            //return false;
            $result['code'] = 500;
            $result['msg'] = $e->getMessage();
            return $result;
        }
        $result['code'] = 500;
        $result['msg'] = 'fail';
        return $result;
    }

    /**
     *解析SQL文件为SQL语句数组
     * @paramstring $path
     * @returnarray|mixed|string
     */
    private function parseSQL($path = '')
    {
        $sql = file_get_contents($path);
        $sql = explode(PHP_EOL, $sql);
        //先消除--注释
        $sql = array_filter($sql, function ($data) {
            if (empty($data) || preg_match('/^--.*/', $data)) {
                return false;
            } else {
                return true;
            }
        });
        $sql = implode('', $sql);
        //删除/**/注释
        //$sql = preg_replace('//*.*.*//', '', $sql);
        return $sql;
    }

    /**
     *下载备份
     * @paramstring $fileName
     * @returnarray|mixed|string
     */
    public function downloadFile($fileName)
    {
        $fileName = $this->config['path'] . $fileName;
        if (file_exists($fileName)) {
            ob_end_clean();
            header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
            header('Content-Description:FileTransfer');
            header('Content-Type:application/octet-stream');
            header('Content-Length:' . filesize($fileName));
            header('Content-Disposition:attachment;filename=' . basename($fileName));
            readfile($fileName);
        } else {
            $this->error = "文件有错误！";
        }
    }

    /**
     *获取文件是时间
     * @paramstring $file
     * @returnstring
     */
    private function getfiletime($file)
    {
        $path = $this->config['path'] . $file;
        $a = filemtime($path);
        $time = date("Y-m-dH:i:s", $a);
        return $time;
    }

    /**
     *获取文件是大小
     * @paramstring $file
     * @returnstring
     */
    private function getfilesize($file)
    {
        $perms = stat($this->config['path'] . $file);
        $size = $perms['size'];
        $a = array('B', 'KB', 'MB', 'GB', 'TB');
        $pos = 0;
        while ($size >= 1024) {
            $size /= 1024;
            $pos++;
        }
        return round($size, 2) . $a[$pos];
    }

    /**
     *获取文件列表
     * @paramstring $Order级别
     * @returnarray
     */
    public function get_filelist($Order = 0)
    {
        $FilePath = $this->config['path'];
        $FilePath = opendir($FilePath);
        $FileAndFolderAyy = array();
        $i = 1;
        while (false !== ($filename = readdir($FilePath))) {
            if ($filename != "." && $filename != "..") {
                $i++;
                $FileAndFolderAyy[$i]['name'] = $filename;
                $FileAndFolderAyy[$i]['time'] = $this->getfiletime($filename);
                $FileAndFolderAyy[$i]['size'] = $this->getfilesize($filename);
            }
        }
        $Order == 0 ? sort($FileAndFolderAyy) : rsort($FileAndFolderAyy);
        return $FileAndFolderAyy;
    }

    public function delfilename($filename)
    {
        $path = $this->config['path'] . $filename;
        if (@unlink($path)) {
            $result['code'] = 200;
            $result['msg'] = "删除成功";
            return $result;
        }
    }
}

/////////测试demo///////////
/*
$table = ['zhl_article', 'zhl_article_detail', 'zhl_sysmenu'];

$config['type'] = 'mysql';
$config['hostname'] = '127.0.0.1';
$config['hostport'] = 3306;
$config['database'] = 'zhlcms2.3';
$config['username'] = 'root';
$config['password'] = '123456';
$config['charset'] = 'utf8';
//$config['backname'] = 'zhlcms2.3221201test.sql';

$sql = new BackDb($config);

/*
$dblist = $sql->get_filelist();//获取文件列表
// $dbfilenum = $this->ShuLiang("databak/");//获取文件数量
$result['dblist'] = $dblist;
// $result['dbfilenum'] = $dbfilenum;
echo json_encode($result);die;
//*/

/*/备份
$r = $sql->backup($table);
var_dump($r);exit;
//*/

/*/ 还原
$fileName = '20221202150259.sql';
$r = $sql->restore($fileName);
var_dump($r);exit;
//*/
